import * as XLSX from 'xlsx';
import { getDeviceParameterDb } from '@/lib/device-parameter-database';
import { getUserManagement } from '@/lib/user-management';

export interface ImportedRecord {
  deviceClass: string;  // 设备类
  objectNumber: string;  // 对象编号（位号）
  objectDescription: string;  // 对象描述
  namingData: Record<string, any>;  // 命名表数据
  positionData: Record<string, any>;  // 位置表数据
  paramsData: Record<string, any>;  // 参数表数据
  errors: string[];  // 验证错误
  warnings: string[];  // 警告信息
  duplicateInfo?: DuplicateInfo;  // 重复信息
  updatePlan?: UpdatePlan;  // 更新计划
}

export interface DuplicateInfo {
  isDuplicate: boolean;
  existingRecord?: any;
  contentIdentical: boolean;
  conflictFields: FieldConflict[];
  newFields: string[];
  unchangedFields: string[];
}

export interface FieldConflict {
  fieldName: string;
  existingValue: any;
  newValue: any;
  canOverwrite: boolean;
  dataSource?: string;
  resolution: 'skip' | 'overwrite' | 'user_choice';
}

export interface UpdatePlan {
  action: 'skip' | 'insert' | 'update' | 'smart_merge' | 'user_choice';
  fieldsToUpdate: string[];
  fieldsToAdd: string[];
  conflicts: FieldConflict[];
  reason: string;
}

export interface ImportResult {
  success: boolean;
  totalRows: number;
  successRows: number;
  errorRows: number;
  skippedRows: number; // 新增：跳过的无效行数
  records: ImportedRecord[];
  summary: {
    duplicateObjectNumbers: string[];
    missingRequiredFields: { row: number; fields: string[] }[];
    invalidDataTypes: { row: number; field: string; value: any; expectedType: string }[];
    skippedInvalidRows: { row: number; reason: string }[]; // 新增：跳过的行详情
  };
}

export interface ImportOptions {
  specialty: string;  // 专业名称
  objectCategory: string;  // 对象类名称
  classificationCode?: string;  // 分类代码（可选，会从specialty和objectCategory推导）
  userRole: string;
  validateOnly?: boolean;  // 只验证不保存
  skipDuplicates?: boolean;  // 跳过重复的对象编号
  duplicateHandling?: 'skip' | 'error' | 'smart_update' | 'user_choice';  // 重复处理方式
  intelligentMerge?: boolean;  // 是否启用智能合并
}

export class TemplateImportService {
  private db = getDeviceParameterDb();
  private userMgmt = getUserManagement();

  /**
   * 导入Excel模板数据
   */
  async importTemplateData(buffer: ArrayBuffer, options: ImportOptions): Promise<ImportResult> {
    const { 
      specialty, 
      objectCategory, 
      classificationCode, 
      userRole, 
      validateOnly = false, 
      skipDuplicates = false,
      duplicateHandling = 'skip',
      intelligentMerge = false 
    } = options;

    try {
      // 解析Excel文件
      const workbook = XLSX.read(buffer, { type: 'array' });
      const dataSheet = workbook.Sheets['数据录入'];
      
      if (!dataSheet) {
        throw new Error('未找到"数据录入"工作表');
      }

      // 获取对象类下的第一个设备映射（因为同对象类设备的属性表结构相同）
      const allMappings = this.db.getAllDeviceTypeMappings();
      const deviceMapping = allMappings.find(mapping => 
        mapping.specialty === specialty && mapping.classification_name === objectCategory
      );
      if (!deviceMapping) {
        throw new Error(`未找到专业"${specialty}"对象类"${objectCategory}"的设备映射`);
      }

      // 获取字段定义
      const fieldDefinitions = await this.getFieldDefinitions(deviceMapping, userRole);

      // 解析数据
      const rawData = XLSX.utils.sheet_to_json(dataSheet, { header: 1 }) as any[][];
      const parseResult = await this.parseExcelData(rawData, fieldDefinitions, userRole);
      const records = parseResult.records;
      const skippedRows = parseResult.skippedRows;

      // 验证数据（使用增强的验证逻辑）
      const finalClassificationCode = classificationCode || deviceMapping.classification_code;
      const validatedRecords = await this.validateRecords(records, finalClassificationCode, options);

      // 保存数据（如果不是只验证模式）
      if (!validateOnly) {
        await this.saveRecords(validatedRecords, deviceMapping, specialty, objectCategory, options);
      }

      // 生成导入结果
      return this.generateImportResult(validatedRecords, skippedRows);

    } catch (error) {
      throw new Error(`导入失败: ${error instanceof Error ? error.message : '未知错误'}`);
    }
  }

  /**
   * 获取字段定义
   */
  private async getFieldDefinitions(deviceMapping: any, userRole: string) {
    const namingFields = this.db.getParameterTableFields(deviceMapping.naming_table);
    const positionFields = this.db.getParameterTableFields(deviceMapping.position_table);
    const paramsFields = this.db.getParameterTableFields(deviceMapping.base_params_table);

    const allFields = [
      ...namingFields.map(f => ({ ...f, table_type: 'naming' })),
      ...positionFields.map(f => ({ ...f, table_type: 'position' })),
      ...paramsFields.map(f => ({ ...f, table_type: 'base_params' }))
    ];

    // 根据用户角色过滤字段
    const allowedDataSources = this.getAllowedDataSources(userRole);
    const filteredFields = allFields.filter(field => {
      if (userRole === 'A') return true;  // 管理员可以看到所有字段
      if (userRole === 'V') return true;  // 查看者可以看到但不能编辑
      return allowedDataSources.includes(field.data_source);
    });

    return filteredFields;
  }

  /**
   * 解析Excel数据
   */
  private async parseExcelData(
    rawData: any[][],
    fieldDefinitions: any[],
    userRole: string
  ): Promise<{ records: ImportedRecord[]; skippedRows: { row: number; reason: string }[] }> {
    if (rawData.length < 2) {
      throw new Error('数据格式不正确，至少需要表头和一行数据');
    }

    const headers = rawData[0];
    
    // 验证表头格式
    if (headers[0] !== '设备类' || headers[1] !== '对象编号' || headers[2] !== '对象描述') {
      throw new Error('表头格式不正确，前三列必须是"设备类"、"对象编号"和"对象描述"');
    }

    // 创建字段映射
    const fieldMap = new Map();
    fieldDefinitions.forEach(field => {
      fieldMap.set(field.field_name, field);
    });

    const records: ImportedRecord[] = [];
    const skippedRows: { row: number; reason: string }[] = [];
    const allowedDataSources = this.getAllowedDataSources(userRole);

    // 从第2行开始处理数据（跳过表头行）
    for (let i = 1; i < rawData.length; i++) {
      const row = rawData[i];
      if (!row || !row[0]) continue; // 跳过空行

      const deviceClass = String(row[0] || '').trim();
      const objectNumber = String(row[1] || '').trim();
      const objectDescription = String(row[2] || '').trim();

      // 过滤无效行：如果只有设备类有数据，而对象编号为空，则跳过此行
      if (deviceClass && !objectNumber && !objectDescription) {
        // 检查其他字段是否也都为空
        let hasOtherData = false;
        for (let j = 3; j < row.length; j++) {
          if (row[j] && String(row[j]).trim() !== '') {
            hasOtherData = true;
            break;
          }
        }
        
        if (!hasOtherData) {
          const reason = `只有设备类有数据，缺少对象编号等关键信息 - ${deviceClass}`;
          console.log(`跳过无效行 ${i + 1}：${reason}`);
          skippedRows.push({ row: i + 1, reason });
          continue; // 跳过这一行
        }
      }

      const record: ImportedRecord = {
        deviceClass,
        objectNumber,
        objectDescription,
        namingData: {},
        positionData: {},
        paramsData: {},
        errors: [],
        warnings: []
      };

      // 处理字段数据（从第4列开始，前三列是设备类、对象编号、对象描述）
      for (let j = 3; j < headers.length; j++) {
        const fieldName = headers[j];
        const fieldDef = fieldMap.get(fieldName);
        const cellValue = row[j];

        if (!fieldDef) {
          record.warnings.push(`未知字段: ${fieldName}`);
          continue;
        }

        // 检查权限
        const canEdit = userRole === 'A' || allowedDataSources.includes(fieldDef.data_source);
        if (!canEdit && cellValue) {
          record.errors.push(`无权限编辑字段: ${fieldName} (数据来源: ${fieldDef.data_source})`);
          continue;
        }

        // 数据类型转换和验证
        const processedValue = this.processFieldValue(cellValue, fieldDef, record);

        // 分配到对应的表数据
        switch (fieldDef.table_type) {
          case 'naming':
            record.namingData[fieldName] = processedValue;
            break;
          case 'position':
            record.positionData[fieldName] = processedValue;
            break;
          case 'base_params':
            record.paramsData[fieldName] = processedValue;
            break;
        }
      }

      // 基本验证
      if (!record.deviceClass) {
        record.errors.push('设备类不能为空');
      }
      
      if (!record.objectNumber) {
        record.errors.push('对象编号不能为空');
      }

      if (!record.objectDescription) {
        record.warnings.push('建议填写对象描述');
      }

      records.push(record);
    }

    return { records, skippedRows };
  }

  /**
   * 处理字段值
   */
  private processFieldValue(value: any, fieldDef: any, record: ImportedRecord): any {
    if (value === null || value === undefined || value === '') {
      if (fieldDef.is_required) {
        record.errors.push(`必填字段不能为空: ${fieldDef.field_name}`);
      }
      return null;
    }

    const stringValue = String(value).trim();

    switch (fieldDef.data_type) {
      case '数值型':
        const numValue = Number(stringValue);
        if (isNaN(numValue)) {
          record.errors.push(`字段 ${fieldDef.field_name} 应为数字，当前值: ${stringValue}`);
          return stringValue;
        }
        return numValue;

      case '日期型':
        const dateValue = new Date(stringValue);
        if (isNaN(dateValue.getTime())) {
          record.errors.push(`字段 ${fieldDef.field_name} 应为有效日期，当前值: ${stringValue}`);
          return stringValue;
        }
        return dateValue.toISOString().split('T')[0];

      case '布尔型':
        const lowerValue = stringValue.toLowerCase();
        if (['是', '否', 'true', 'false', '1', '0', 'yes', 'no'].includes(lowerValue)) {
          return ['是', 'true', '1', 'yes'].includes(lowerValue);
        }
        record.errors.push(`字段 ${fieldDef.field_name} 应为布尔值，当前值: ${stringValue}`);
        return stringValue;

      default: // 文本型
        return stringValue;
    }
  }

  /**
   * 验证记录
   */
  private async validateRecords(records: ImportedRecord[], classificationCode: string, options: ImportOptions): Promise<ImportedRecord[]> {
    const objectNumbers = new Set<string>();
    const duplicates = new Set<string>();

    // 检查重复的对象编号
    records.forEach(record => {
      if (objectNumbers.has(record.objectNumber)) {
        duplicates.add(record.objectNumber);
        record.errors.push(`对象编号重复: ${record.objectNumber}`);
      } else {
        objectNumbers.add(record.objectNumber);
      }
    });

    // 执行高级重复检测和内容比较
    for (const record of records) {
      const duplicateInfo = await this.performAdvancedDuplicateDetection(record, classificationCode, options);
      record.duplicateInfo = duplicateInfo;
      
      if (duplicateInfo.isDuplicate) {
        const updatePlan = await this.generateIntelligentUpdatePlan(record, duplicateInfo, options);
        record.updatePlan = updatePlan;
        
        // 根据处理策略决定是否添加错误或警告
        if (options.duplicateHandling === 'error') {
          record.errors.push(`对象编号在数据库中已存在: ${record.objectNumber}`);
        } else if (options.duplicateHandling === 'skip') {
          record.warnings.push(`对象编号已存在，将跳过: ${record.objectNumber}`);
        } else if (options.duplicateHandling === 'smart_update') {
          if (duplicateInfo.contentIdentical) {
            record.warnings.push(`对象编号已存在且内容一致，将跳过: ${record.objectNumber}`);
          } else {
            record.warnings.push(`对象编号已存在但内容不同，将智能更新: ${record.objectNumber}`);
          }
        }
      }
    }

    return records;
  }

  /**
   * 检查对象编号是否已存在
   */
  private async checkObjectNumberExists(objectNumber: string, classificationCode: string): Promise<boolean> {
    try {
      // 这里需要检查设备表中是否已存在该对象编号
      const stmt = this.db['db'].prepare(`
        SELECT COUNT(*) as count FROM devices 
        WHERE name = ? AND classification_code = ?
      `);
      const result = stmt.get(objectNumber, classificationCode) as { count: number };
      return result.count > 0;
    } catch (error) {
      console.error('检查对象编号失败:', error);
      return false;
    }
  }

  /**
   * 执行高级重复检测
   */
  private async performAdvancedDuplicateDetection(
    record: ImportedRecord, 
    classificationCode: string, 
    options: ImportOptions
  ): Promise<DuplicateInfo> {
    try {
      // 尝试找到现有记录
      const existingRecord = await this.getExistingRecord(record.objectNumber, classificationCode, options);
      
      if (!existingRecord) {
        return {
          isDuplicate: false,
          contentIdentical: false,
          conflictFields: [],
          newFields: [],
          unchangedFields: []
        };
      }

      // 比较记录内容
      const comparison = await this.compareRecordContent(record, existingRecord);
      
      return {
        isDuplicate: true,
        existingRecord,
        contentIdentical: comparison.identical,
        conflictFields: comparison.conflicts,
        newFields: comparison.newFields,
        unchangedFields: comparison.unchangedFields
      };
    } catch (error) {
      console.error('高级重复检测失败:', error);
      return {
        isDuplicate: false,
        contentIdentical: false,
        conflictFields: [],
        newFields: [],
        unchangedFields: []
      };
    }
  }

  /**
   * 获取现有记录
   */
  private async getExistingRecord(objectNumber: string, classificationCode: string, options: ImportOptions): Promise<any> {
    try {
      // 构建实例表名
      const instanceTableName = this.buildInstanceTableName(options.specialty, options.objectCategory);
      
      const stmt = this.db['db'].prepare(`
        SELECT * FROM [${instanceTableName}] 
        WHERE object_number = ?
        LIMIT 1
      `);
      
      return stmt.get(objectNumber);
    } catch (error) {
      console.error('获取现有记录失败:', error);
      return null;
    }
  }

  /**
   * 比较记录内容
   */
  private async compareRecordContent(newRecord: ImportedRecord, existingRecord: any): Promise<{
    identical: boolean;
    conflicts: FieldConflict[];
    newFields: string[];
    unchangedFields: string[];
  }> {
    const conflicts: FieldConflict[] = [];
    const newFields: string[] = [];
    const unchangedFields: string[] = [];

    // 合并所有新记录的数据
    const newData: Record<string, any> = {
      object_description: newRecord.objectDescription,
      ...newRecord.namingData,
      ...newRecord.positionData,
      ...newRecord.paramsData
    };

    // 比较每个字段
    for (const [fieldName, newValue] of Object.entries(newData)) {
      const existingValue = existingRecord[fieldName];
      
      // 跳过空值
      if (newValue === null || newValue === undefined || newValue === '') {
        continue;
      }

      if (existingValue === null || existingValue === undefined || existingValue === '') {
        // 现有记录该字段为空，新记录有值 - 这是一个可以填充的新字段
        newFields.push(fieldName);
      } else if (this.normalizeValue(existingValue) === this.normalizeValue(newValue)) {
        // 值相同
        unchangedFields.push(fieldName);
      } else {
        // 值不同 - 这是一个冲突
        conflicts.push({
          fieldName,
          existingValue,
          newValue,
          canOverwrite: true, // 这里可以根据数据源权限等规则来决定
          resolution: 'user_choice'
        });
      }
    }

    return {
      identical: conflicts.length === 0 && newFields.length === 0,
      conflicts,
      newFields,
      unchangedFields
    };
  }

  /**
   * 生成智能更新计划
   */
  private async generateIntelligentUpdatePlan(
    record: ImportedRecord, 
    duplicateInfo: DuplicateInfo, 
    options: ImportOptions
  ): Promise<UpdatePlan> {
    if (!duplicateInfo.isDuplicate) {
      return {
        action: 'insert',
        fieldsToUpdate: [],
        fieldsToAdd: [],
        conflicts: [],
        reason: '新记录，执行插入'
      };
    }

    if (duplicateInfo.contentIdentical) {
      return {
        action: 'skip',
        fieldsToUpdate: [],
        fieldsToAdd: [],
        conflicts: [],
        reason: '内容完全一致，跳过导入'
      };
    }

    // 如果只有新字段没有冲突，执行智能合并
    if (duplicateInfo.conflictFields.length === 0 && duplicateInfo.newFields.length > 0) {
      return {
        action: 'smart_merge',
        fieldsToUpdate: [],
        fieldsToAdd: duplicateInfo.newFields,
        conflicts: [],
        reason: `自动填充${duplicateInfo.newFields.length}个空字段`
      };
    }

    // 有冲突的情况
    if (duplicateInfo.conflictFields.length > 0) {
      if (options.duplicateHandling === 'smart_update' && options.intelligentMerge) {
        // 尝试智能解决冲突
        const autoResolvableConflicts = duplicateInfo.conflictFields.filter(c => c.canOverwrite);
        
        return {
          action: 'update',
          fieldsToUpdate: autoResolvableConflicts.map(c => c.fieldName),
          fieldsToAdd: duplicateInfo.newFields,
          conflicts: duplicateInfo.conflictFields.filter(c => !c.canOverwrite),
          reason: `智能更新${autoResolvableConflicts.length}个字段，填充${duplicateInfo.newFields.length}个空字段`
        };
      } else {
        return {
          action: 'user_choice',
          fieldsToUpdate: [],
          fieldsToAdd: duplicateInfo.newFields,
          conflicts: duplicateInfo.conflictFields,
          reason: '存在字段冲突，需要用户选择'
        };
      }
    }

    return {
      action: 'skip',
      fieldsToUpdate: [],
      fieldsToAdd: [],
      conflicts: [],
      reason: '无法确定更新策略'
    };
  }

  /**
   * 标准化值用于比较
   */
  private normalizeValue(value: any): string {
    if (value === null || value === undefined) return '';
    return String(value).trim();
  }

  /**
   * 保存记录到数据库
   */
  private async saveRecords(
    records: ImportedRecord[],
    deviceMapping: any,
    specialty: string,
    objectCategory: string,
    options: ImportOptions
  ): Promise<void> {
    const validRecords = records.filter(record => {
      if (record.errors.length > 0) return false;
      
      // 根据新的重复处理策略过滤记录
      if (options.skipDuplicates && record.warnings.some(w => w.includes('已存在'))) return false;
      
      // 如果有更新计划且计划是跳过，则过滤掉
      if (record.updatePlan?.action === 'skip') return false;
      
      return true;
    });

    // 开始事务
    const db = this.db['db'];
    const transaction = db.transaction(() => {
      for (const record of validRecords) {
        try {
          // 根据更新计划执行不同的操作
          if (record.updatePlan) {
            switch (record.updatePlan.action) {
              case 'insert':
                this.saveDeviceRecordWithAttributes(record, deviceMapping, specialty, objectCategory);
                console.log(`新记录插入成功: ${record.objectNumber}`);
                break;
              
              case 'smart_merge':
                this.updateExistingRecord(record, specialty, objectCategory, 'merge');
                console.log(`智能合并完成: ${record.objectNumber}`);
                break;
              
              case 'update':
                this.updateExistingRecord(record, specialty, objectCategory, 'update');
                console.log(`记录更新完成: ${record.objectNumber}`);
                break;
              
              default:
                // 默认插入新记录
                this.saveDeviceRecordWithAttributes(record, deviceMapping, specialty, objectCategory);
            }
          } else {
            // 没有更新计划，执行默认插入
            this.saveDeviceRecordWithAttributes(record, deviceMapping, specialty, objectCategory);
          }

        } catch (error) {
          record.errors.push(`保存失败: ${error instanceof Error ? error.message : '未知错误'}`);
          throw error;
        }
      }
    });

    transaction();
  }

  /**
   * 保存设备实例记录（插入到对应的实例表，而不是devices表）
   */
  private saveDeviceRecordWithAttributes(record: ImportedRecord, deviceMapping: any, specialty: string, objectCategory: string): number {
    const db = this.db['db'];
    
    console.log('=== 保存设备实例记录开始 ===');
    console.log('设备类:', record.deviceClass);
    console.log('对象编号:', record.objectNumber);
    console.log('对象描述:', record.objectDescription);
    console.log('专业:', specialty);
    console.log('对象类:', objectCategory);
    console.log('设备映射:', deviceMapping);
    
    // 查找目标设备类的device_id（根据专业、对象类和设备类名称）
    const deviceStmt = db.prepare(`
      SELECT device_id FROM devices 
      WHERE specialty = ? AND classification_name = ? AND name = ?
    `);
    const targetDevice = deviceStmt.get(specialty, objectCategory, record.deviceClass) as { device_id: string } | undefined;
    
    if (!targetDevice) {
      throw new Error(`未找到匹配的设备类：专业=${specialty}, 对象类=${objectCategory}, 设备类=${record.deviceClass}`);
    }
    
    const deviceId = targetDevice.device_id;
    
    // 实例表按专业+对象类的英文名称命名
    if (!deviceMapping.english_name) {
      throw new Error(`对象类 ${deviceMapping.classification_name} 缺少英文名称，无法创建实例表`);
    }
    
    // 获取专业前缀
    const specialtyPrefixes: Record<string, string> = {
      "配管专业": "piping",
      "电气专业": "electrical", 
      "暖通专业": "hvac",
      "通信专业": "communication",
      "仪表专业": "instrumentation"
    };
    
    const specialtyPrefix = specialtyPrefixes[deviceMapping.specialty];
    if (!specialtyPrefix) {
      throw new Error(`未知专业: ${deviceMapping.specialty}`);
    }
    
    const instanceTableName = `device_instances_${specialtyPrefix}_${deviceMapping.english_name}`;
    
    console.log('目标设备ID:', deviceId);
    console.log('对象类分类码:', deviceMapping.classification_code);
    console.log('专业:', deviceMapping.specialty);
    console.log('对象类英文名称:', deviceMapping.english_name);
    console.log('实例表名:', instanceTableName);
    
    // 检查实例表是否存在
    const tableExistsStmt = db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name=?
    `);
    const tableExists = tableExistsStmt.get(instanceTableName);
    
    if (!tableExists) {
      throw new Error(`实例表不存在: ${instanceTableName}`);
    }
    
    // 生成新的实例编号
    const maxInstanceStmt = db.prepare(`
      SELECT MAX(CAST(instance_number AS INTEGER)) as max_num 
      FROM [${instanceTableName}]
    `);
    const maxResult = maxInstanceStmt.get() as { max_num: number | null };
    const nextInstanceNumber = ((maxResult?.max_num || 0) + 1).toString().padStart(3, '0');
    
    console.log('新实例编号:', nextInstanceNumber);
    
    // 准备插入参数
    const cleanString = (str: string) => typeof str === 'string' ? str.replace(/[\\"]/g, '') : str;
    
    // 获取实例表的字段映射信息
    const fieldMappingStmt = db.prepare(`
      SELECT field_name, source_table_type FROM instance_field_mappings
      WHERE instance_table_name = ?
      ORDER BY field_name
    `);
    const fieldMappings = fieldMappingStmt.all(instanceTableName) as Array<{field_name: string, source_table_type: string}>;
    
    console.log('字段映射数量:', fieldMappings.length);
    
    // 准备基础字段和属性字段的数据
    const baseColumns = ['device_type_code', 'instance_number', 'object_number', 'object_description', 'created_at', 'updated_at'];
    const baseValues = [
      deviceId,
      nextInstanceNumber,
      cleanString(record.objectNumber),
      cleanString(record.objectDescription)
      // created_at 和 updated_at 将在SQL中使用 datetime('now')
    ];
    
    // 准备属性字段的数据
    const attributeColumns: string[] = [];
    const attributeValues: any[] = [];
    
    for (const mapping of fieldMappings) {
      const fieldName = mapping.field_name;
      const sourceType = mapping.source_table_type;
      
      // 从对应的属性数据中获取值
      let value = null;
      const originalFieldName = fieldName.replace(`${sourceType}_`, '');
      
      if (sourceType === 'naming' && record.namingData[originalFieldName] !== undefined) {
        value = record.namingData[originalFieldName];
      } else if (sourceType === 'position' && record.positionData[originalFieldName] !== undefined) {
        value = record.positionData[originalFieldName];
      } else if (sourceType === 'base_params' && record.paramsData[originalFieldName] !== undefined) {
        value = record.paramsData[originalFieldName];
      }
      
      attributeColumns.push(`[${fieldName}]`);
      attributeValues.push(value);
    }
    
    // 合并所有字段和值
    const allColumns = [...baseColumns.map(c => `[${c}]`), ...attributeColumns];
    const allValues = [...baseValues, ...attributeValues]; // 所有实际值
    const allPlaceholders = [...baseValues.map(() => '?'), ...attributeValues.map(() => '?'), "datetime('now')", "datetime('now')"];
    
    console.log('总字段数:', allColumns.length);
    console.log('总值数:', allValues.length);
    console.log('属性字段数:', attributeColumns.length);
    console.log('属性值数:', attributeValues.length);
    
    // 构建插入SQL
    const insertSQL = `
      INSERT INTO [${instanceTableName}] (${allColumns.join(', ')})
      VALUES (${allPlaceholders.join(', ')})
    `;
    
    console.log('插入SQL:', insertSQL);
    console.log('插入参数:', allValues);
    
    try {
      const result = db.prepare(insertSQL).run(...allValues);
      console.log('设备实例创建成功，包含属性数据。ID:', result.lastInsertRowid);
      return result.lastInsertRowid as number;
    } catch (error) {
      console.error('插入设备实例失败:', error);
      console.error('失败的SQL:', insertSQL);
      console.error('失败的参数:', allValues);
      throw error;
    }
  }

  /**
   * 获取专业前缀
   */
  private getSpecialtyPrefix(specialty: string): string {
    const prefixMap: Record<string, string> = {
      '配管专业': '配管',
      '电气专业': '电气',
      '暖通专业': '暖通',
      '通信专业': '通信',
      '仪表专业': '仪表'
    };
    return prefixMap[specialty] || specialty.substring(0, 2);
  }

  /**
   * 获取下一个设备序号
   */
  private getNextDeviceSequence(specialtyPrefix: string): number {
    const db = this.db['db'];
    const stmt = db.prepare(`
      SELECT COUNT(*) as count FROM devices 
      WHERE device_id LIKE ?
    `);
    const result = stmt.get(`${specialtyPrefix}-%`) as { count: number };
    return result.count + 1;
  }

  /**
   * 格式化表ID（从table_cx格式转换为表 C.x格式）
   */
  private formatTableId(tableName: string): string {
    if (!tableName || !tableName.startsWith('table_c')) {
      return tableName;
    }
    
    const number = tableName.replace('table_c', '');
    return `表 C.${number}`;
  }

  /**
   * 保存参数数据
   */
  private saveParameterData(deviceId: number, tableName: string, data: Record<string, any>): number | null {
    if (Object.keys(data).length === 0) {
      console.log(`跳过空参数数据 (设备ID: ${deviceId}, 表: ${tableName})`);
      return null;
    }

    console.log(`=== 保存参数数据开始 ===`);
    console.log(`设备ID: ${deviceId}, 表名: ${tableName}`);
    console.log('参数数据:', data);

    try {
      // 使用DeviceParameterDatabase的方法创建参数记录
      console.log('调用 createParameterRecord...');
      const recordId = this.db.createParameterRecord(deviceId, tableName, data);
      console.log('参数记录创建成功，记录ID:', recordId);
      
      // 更新设备表中的关联字段
      console.log('更新设备参数引用...');
      this.updateDeviceParameterReference(deviceId, tableName, recordId);
      console.log('设备参数引用更新完成');
      
      return recordId;
    } catch (error) {
      console.error(`保存参数数据失败 (设备ID: ${deviceId}, 表: ${tableName}):`, error);
      console.error('失败的数据:', data);
      throw error;
    }
  }

  /**
   * 更新设备的参数记录引用
   */
  private updateDeviceParameterReference(deviceId: number, tableName: string, recordId: number): void {
    const db = this.db['db'];
    
    console.log(`=== 更新设备参数引用 ===`);
    console.log(`设备ID: ${deviceId}, 表名: ${tableName}, 记录ID: ${recordId}`);
    
    // 根据表名确定要更新的字段
    let updateSql: string;
    let fieldName: string;
    if (tableName.includes('naming') || tableName === 'table_c2') {
      updateSql = `UPDATE devices SET naming_record_id = ?, updated_at = datetime('now') WHERE id = ?`;
      fieldName = 'naming_record_id';
    } else if (tableName.includes('position') || tableName === 'table_c4') {
      updateSql = `UPDATE devices SET position_record_id = ?, updated_at = datetime('now') WHERE id = ?`;
      fieldName = 'position_record_id';
    } else {
      updateSql = `UPDATE devices SET base_params_record_id = ?, updated_at = datetime('now') WHERE id = ?`;
      fieldName = 'base_params_record_id';
    }

    console.log(`更新字段: ${fieldName}`);
    console.log(`SQL: ${updateSql}`);
    console.log(`参数: [${recordId}, ${deviceId}]`);

    try {
      const updateStmt = db.prepare(updateSql);
      const result = updateStmt.run(recordId, deviceId);
      console.log('设备参数引用更新成功，影响行数:', result.changes);
    } catch (error) {
      console.error('更新设备参数引用失败:', error);
      console.error('失败的SQL:', updateSql);
      console.error('失败的参数:', [recordId, deviceId]);
      throw error;
    }
  }

  /**
   * 更新现有记录
   */
  private updateExistingRecord(
    record: ImportedRecord, 
    specialty: string, 
    objectCategory: string, 
    updateType: 'merge' | 'update'
  ): void {
    const db = this.db['db'];
    
    try {
      // 构建实例表名
      const instanceTableName = this.buildInstanceTableName(specialty, objectCategory);
      
      // 准备更新的字段和值
      const updateFields: string[] = [];
      const updateValues: any[] = [];
      
      // 合并所有新记录的数据
      const newData: Record<string, any> = {
        object_description: record.objectDescription,
        ...record.namingData,
        ...record.positionData,
        ...record.paramsData
      };
      
      // 根据更新计划决定更新哪些字段
      if (record.updatePlan) {
        if (updateType === 'merge') {
          // 智能合并：只更新空字段
          record.updatePlan.fieldsToAdd.forEach(fieldName => {
            if (newData[fieldName] !== null && newData[fieldName] !== undefined && newData[fieldName] !== '') {
              updateFields.push(`[${fieldName}] = ?`);
              updateValues.push(newData[fieldName]);
            }
          });
        } else if (updateType === 'update') {
          // 更新：更新指定的字段和新字段
          [...record.updatePlan.fieldsToUpdate, ...record.updatePlan.fieldsToAdd].forEach(fieldName => {
            if (newData[fieldName] !== null && newData[fieldName] !== undefined && newData[fieldName] !== '') {
              updateFields.push(`[${fieldName}] = ?`);
              updateValues.push(newData[fieldName]);
            }
          });
        }
      }
      
      if (updateFields.length === 0) {
        console.log(`没有字段需要更新: ${record.objectNumber}`);
        return;
      }
      
      // 添加更新时间戳
      updateFields.push('[updated_at] = ?');
      updateValues.push(new Date().toISOString());
      
      // 构建并执行更新SQL
      const updateSql = `
        UPDATE [${instanceTableName}] 
        SET ${updateFields.join(', ')} 
        WHERE object_number = ?
      `;
      updateValues.push(record.objectNumber);
      
      const updateStmt = db.prepare(updateSql);
      const result = updateStmt.run(...updateValues);
      
      console.log(`记录更新成功: ${record.objectNumber}, 影响行数: ${result.changes}`);
      
    } catch (error) {
      console.error('更新现有记录失败:', error);
      throw error;
    }
  }

  /**
   * 构建实例表名
   */
  private buildInstanceTableName(specialty: string, objectCategory: string): string {
    // 将专业和对象类转换为表名格式
    const specialtyMap: Record<string, string> = {
      '配管专业': 'piping',
      '电气专业': 'electrical', 
      '暖通专业': 'hvac',
      '通信专业': 'communication',
      '仪表专业': 'instrumentation'
    };
    
    const specialtyCode = specialtyMap[specialty] || specialty.toLowerCase();
    const categoryCode = objectCategory.toLowerCase().replace(/[^a-z0-9]/g, '_');
    
    return `device_instances_${specialtyCode}_${categoryCode}`;
  }

  /**
   * 生成导入结果
   */
  private generateImportResult(records: ImportedRecord[], skippedRows: { row: number; reason: string }[] = []): ImportResult {
    const successRows = records.filter(r => r.errors.length === 0).length;
    const errorRows = records.filter(r => r.errors.length > 0).length;

    const duplicateObjectNumbers = Array.from(
      new Set(
        records
          .filter(r => r.errors.some(e => e.includes('重复')))
          .map(r => r.objectNumber)
      )
    );

    const missingRequiredFields: { row: number; fields: string[] }[] = [];
    const invalidDataTypes: { row: number; field: string; value: any; expectedType: string }[] = [];

    records.forEach((record, index) => {
      const requiredFieldErrors = record.errors
        .filter(e => e.includes('必填字段'))
        .map(e => e.replace('必填字段不能为空: ', ''));
      
      if (requiredFieldErrors.length > 0) {
        missingRequiredFields.push({
          row: index + 2, // Excel行号（从1开始，加上1行表头）
          fields: requiredFieldErrors
        });
      }

      record.errors.forEach(error => {
        if (error.includes('应为')) {
          const match = error.match(/字段 (.+) 应为(.+)，当前值: (.+)/);
          if (match) {
            invalidDataTypes.push({
              row: index + 2, // Excel行号（从1开始，加上1行表头）
              field: match[1],
              value: match[3],
              expectedType: match[2]
            });
          }
        }
      });
    });

    return {
      success: errorRows === 0,
      totalRows: records.length,
      successRows,
      errorRows,
      skippedRows: skippedRows.length,
      records,
      summary: {
        duplicateObjectNumbers,
        missingRequiredFields,
        invalidDataTypes,
        skippedInvalidRows: skippedRows
      }
    };
  }

  /**
   * 获取用户角色允许的数据来源
   */
  private getAllowedDataSources(userRole: string): string[] {
    switch (userRole) {
      case 'A': return ['E', 'P', 'M', 'C', 'R', 'O']; // 管理员
      case 'E': return ['E']; // 设计
      case 'P': return ['P']; // 采购
      case 'M': return ['M']; // 监测
      case 'C': return ['C']; // 施工
      case 'O': return ['O']; // 建设
      case 'R': return ['R']; // 运维
      case 'V': return []; // 查看者（不能编辑）
      default: return [];
    }
  }
}

// 导出单例实例
export const templateImportService = new TemplateImportService();